将指定excel的一列数据提取到另一个excel的指定列

#!/usr/bin/env python
import openpyxl
bj='D:/地市县公司/西藏台账数据分析-设备台帐分析.xlsx'
wb = openpyxl.load_workbook (bj)
get_sheets = wb.sheetnames
#print(get_sheets)
TA01='TA01'
TA02='TA02'
TA03='TA03'
TE01='TE01'
YG201='YG201'
YG202='YG202'
YG203='YG203'
YG204='YG204'
YG205='YG205'
a=0
max=0
for  name in get_sheets:
    ws = wb[name]
    sheet = wb.active
    a = 0
    num=1
    for row in ws:
        if not all([cell.value == None for cell in row]):
            num += 1
    i=0
    b = []
    total=0
    while i < num:
      if TE01 == name:
      #  b.insert(i, ws.cell(i+5,2).value)
        b.append(ws.cell(i+5,2).value)
        total = num-5
        i = i+1
        #break
      elif TA01 == name:
        #b.insert(i, ws.cell(i + 4, 2).value)
        b.append(ws.cell(i + 4, 2).value)
        total = num - 4
        i = i + 1
      elif TA02 == name:
       # b.insert(i, ws.cell(i + 5, 2).value)
        b.append(ws.cell(i + 5, 2).value)
        total = num - 5
        i = i + 1
        #break
      elif TA03 == name:
       # b.insert(i, ws.cell(i + 4, 2).value)
        b.append(ws.cell(i + 4, 2).value)
        total = num - 4
        i = i + 1
       # break
      elif YG201 == name:
        #b.insert(i, ws.cell(i + 7, 2).value)
        b.append(ws.cell(i + 7, 2).value)
        total = num - 7
        i = i + 1
        #break
      elif YG202 == name:
      #  b.insert(i, ws.cell(i + 7, 2).value)
        b.append(ws.cell(i + 7, 2).value)
        total = num - 7
        i = i + 1
        #break
      elif YG203 == name:
        #b.insert(i, ws.cell(i + 7, 2).value)
        b.append(ws.cell(i + 7, 2).value)
        total = num - 7
        i = i + 1
        #break
      elif YG204 == name:
       # b.insert(i, ws.cell(i + 7, 2).value)
        b.append(ws.cell(i + 7, 2).value)
        total = num - 7
        i = i + 1
        #break
      elif YG205==name:
       # b.insert(i, ws.cell(i + 7, 2).value)
        b.append(ws.cell(i + 7, 2).value)
        total = num - 7
        i = i + 1
        #break
    #print(b)
    c= [ii for ii in b if ii is not None]
    #print(c)
    num1=1
    if TE01 == name:
        wbtest = openpyxl.load_workbook('D:/定员测试数据收集表/1-1客户服务台账数据.xlsx')
        ws = wbtest['供电公司-客户服务台账数据']
        sheet = wb.active
        for row1 in ws:
            if not all([cell1.value == None for cell1 in row1]):
              num1 += 1
        #break
    elif TA01==name:
        wbtest = openpyxl.load_workbook('D:/定员测试数据收集表/1-2电力调度台账数据.xlsx')
        ws = wbtest['供电公司-电力调度台账数据']
        sheet = wb.active
        for row1 in ws:
          if not all([cell1.value == None for cell1 in row1]):
              num1 += 1
    elif TA02 == name:
        wbtest = openpyxl.load_workbook('D:/定员测试数据收集表/1-3输电运检台账数据.xlsx')
        ws = wbtest['供电公司-输电运检台账数据']
        sheet = wb.active
        for row1 in ws:
           if not all([cell1.value == None for cell1 in row1]):
               num1 += 1
        #break
    elif TA03 == name:
        wbtest = openpyxl.load_workbook('D:/定员测试数据收集表/1-4变电运检台账数据.xlsx')
        ws = wbtest['供电公司-变电运检台账数据']
        sheet = wb.active
        for row1 in ws:
           if not all([cell1.value == None for cell1 in row1]):
              num1 += 1
       # break
    elif YG201 == name:
        wbtest = openpyxl.load_workbook('D:/定员测试数据收集表/2-1前端供电服务实际用工数据.xlsx')
        ws = wbtest['供电公司-前端供电服务-实际用工']
        sheet = wb.active
        for row1 in ws:
          if not all([cell1.value == None for cell1 in row1]):
             num1 += 1
    elif YG202 == name:
        wbtest = openpyxl.load_workbook('D:/定员测试数据收集表/2-2供电服务支撑实际用工数据.xlsx')
        ws = wbtest['供电公司-供电服务支撑-实际用工']
        sheet = wb.active
        for row1 in ws:
          if not all([cell1.value == None for cell1 in row1]):
              num1 += 1
    elif YG203 == name:
        wbtest = openpyxl.load_workbook('D:/定员测试数据收集表/2-3电力调度实际用工数据.xlsx')
        ws = wbtest['供电公司-电力调度-实际用工']
        sheet = wb.active
        for row1 in ws:
          if not all([cell1.value == None for cell1 in row1]):
             num1 += 1
    elif YG204 == name:
        wbtest = openpyxl.load_workbook('D:/定员测试数据收集表/2-4输电运检实际用工数据.xlsx')
        ws = wbtest['供电公司-输电运检-实际用工']
        sheet = wb.active
        for row1 in ws:
           if not all([cell1.value == None for cell1 in row1]):
              num1 += 1
    elif YG205 == name:
        wbtest = openpyxl.load_workbook('D:/定员测试数据收集表/2-5变电运检实际用工数据.xlsx')
        ws = wbtest['供电公司-变电运检-实际用工']
        sheet = wb.active
        print(c)
        for row1 in ws:
            if not all([cell1.value == None for cell1 in row1]):
                num1 += 1

    iii=0
    while iii < len(c):

        ws.cell(row=num1+iii, column=2).value = c[iii]
      #  print( c[iii])
        #print(ws.cell(row=num1+iii, column=2).value)
        #ws.cell(row=num1+iii, column=1).value = 1+iii
        iii = iii+1
   # print(c)
    #print(num1)
    #print(TA01)
    if TE01 == name:
      #  print(num1)
        wbtest.save('D:/定员测试数据收集表/1-1客户服务台账数据.xlsx')
    elif TA01 == name:
       # print(name)
        wbtest.save('D:/定员测试数据收集表/1-2电力调度台账数据.xlsx')
    elif TA02 == name:
        wbtest.save('D:/定员测试数据收集表/1-3输电运检台账数据.xlsx')
    elif TA03 == name:
        wbtest.save('D:/定员测试数据收集表/1-4变电运检台账数据.xlsx')
    elif YG201 == name:
        wbtest.save('D:/定员测试数据收集表/2-1前端供电服务实际用工数据.xlsx')
    elif YG202 == name:
        wbtest.save('D:/定员测试数据收集表/2-2供电服务支撑实际用工数据.xlsx')
    elif YG203 == name:
        wbtest.save('D:/定员测试数据收集表/2-3电力调度实际用工数据.xlsx')
    elif YG204 == name:
        wbtest.save('D:/定员测试数据收集表/2-4输电运检实际用工数据.xlsx')
    elif YG205 == name:
        wbtest.save('D:/定员测试数据收集表/2-5变电运检实际用工数据.xlsx')